﻿namespace SmartSoft.DAL
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using NLog;

    public class CompanyDataAccess
    {
        private static Logger log = LogManager.GetCurrentClassLogger();
        public void CompanyRegister(int pi_English, string ps_Code, ref bool pb_Existed, ref bool pb_Checked, ref bool pb_ParentChecked, ref string ps_Ten_Dvcs, ref string ps_Dia_Chi, ref string ps_Ms_Thue, ref string ps_Tk_NH, ref string ps_Chu_Tk, ref string ps_Ten_NH, ref string ps_Ten_TP, ref DateTime pd_Ngay_Bd_Ht, ref DateTime pd_Ngay_Kt_Ht, ref string ps_Ma_Th)
        {
            SqlParameter[] values = new SqlParameter[15];
            values[0] = new SqlParameter("p_English", pi_English);
            values[1] = new SqlParameter("p_Ma_Dvcs", ps_Code);
            values[2] = new SqlParameter("p_Existed", (bool) pb_Existed);
            values[2].Direction = ParameterDirection.Output;
            values[2].SqlDbType = SqlDbType.Bit;
            values[3] = new SqlParameter("p_Checked", (bool) pb_Checked);
            values[3].Direction = ParameterDirection.Output;
            values[3].SqlDbType = SqlDbType.Bit;
            values[4] = new SqlParameter("p_ParentChecked", (bool) pb_ParentChecked);
            values[4].Direction = ParameterDirection.Output;
            values[4].SqlDbType = SqlDbType.Bit;
            values[5] = new SqlParameter("p_Ten_DvCs", ps_Ten_Dvcs);
            values[5].Direction = ParameterDirection.Output;
            values[5].SqlDbType = SqlDbType.NVarChar;
            values[5].Size = 0x80;
            values[6] = new SqlParameter("p_Dia_Chi", ps_Dia_Chi);
            values[6].Direction = ParameterDirection.Output;
            values[6].SqlDbType = SqlDbType.NVarChar;
            values[6].Size = 0x80;
            values[7] = new SqlParameter("p_Ms_Thue", ps_Ms_Thue);
            values[7].Direction = ParameterDirection.Output;
            values[7].SqlDbType = SqlDbType.NVarChar;
            values[7].Size = 20;
            values[8] = new SqlParameter("p_Tk_NH", ps_Tk_NH);
            values[8].Direction = ParameterDirection.Output;
            values[8].SqlDbType = SqlDbType.NVarChar;
            values[8].Size = 0x20;
            values[9] = new SqlParameter("p_Chu_Tk", ps_Chu_Tk);
            values[9].Direction = ParameterDirection.Output;
            values[9].SqlDbType = SqlDbType.NVarChar;
            values[9].Size = 0x80;
            values[10] = new SqlParameter("p_Ten_NH", ps_Ten_NH);
            values[10].Direction = ParameterDirection.Output;
            values[10].SqlDbType = SqlDbType.NVarChar;
            values[10].Size = 0x80;
            values[11] = new SqlParameter("p_Ten_TP", ps_Ten_TP);
            values[11].Direction = ParameterDirection.Output;
            values[11].SqlDbType = SqlDbType.NVarChar;
            values[11].Size = 0x80;
            values[12] = new SqlParameter("p_Ngay_Bd_Ht", (DateTime) pd_Ngay_Bd_Ht);
            values[12].Direction = ParameterDirection.Output;
            values[12].SqlDbType = SqlDbType.DateTime;
            values[13] = new SqlParameter("p_Ngay_Kt_Ht", (DateTime) pd_Ngay_Kt_Ht);
            values[13].Direction = ParameterDirection.Output;
            values[13].SqlDbType = SqlDbType.DateTime;

            values[14] = new SqlParameter("p_Ma_Th", ps_Ma_Th);
            values[14].Direction = ParameterDirection.Output;
            values[14].SqlDbType = SqlDbType.NChar;

            SqlConnection SSConnection = SSDataAccess.SSConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "DmDvCs_One_Get";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = SSConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
                pb_Existed = (bool) values[2].Value;
                if (pb_Existed)
                {
                    pb_Checked = (bool) values[3].Value;
                    pb_ParentChecked = (bool) values[4].Value;
                    ps_Ten_Dvcs = (string) values[5].Value;
                    ps_Dia_Chi = (string) values[6].Value;
                    ps_Ms_Thue = (string) values[7].Value;
                    ps_Tk_NH = (string) values[8].Value;
                    ps_Chu_Tk = (string) values[9].Value;
                    ps_Ten_NH = (string) values[10].Value;
                    ps_Ten_TP = (string) values[11].Value;
                    pd_Ngay_Bd_Ht = (DateTime) values[12].Value;
                    pd_Ngay_Kt_Ht = (DateTime) values[13].Value;
                    ps_Ma_Th = (string)values[14].Value;
                }
            }
            catch (Exception exception)
            {
                log.ErrorException(string.Empty, exception); 
                throw new NotSupportedException("Invalid expression", exception);
            }
            finally
            {
                SSDataAccess.Close(SSConnection);
            }
        }

        public DataTable GetCompanies()
        {
            DataTable table2;
            SqlConnection SSConnection = SSDataAccess.SSConnection;
            DataTable dataTable = new DataTable("VBSP");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "DmDvCs_Get";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = SSConnection;
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
                table2 = dataTable;
            }
            catch (Exception exception)
            {
                log.ErrorException(string.Empty, exception); 
                throw new NotSupportedException("Invalid expression", exception);
            }
            finally
            {
                SSDataAccess.Close(SSConnection);
            }
            return table2;
        }

        public DataTable GetCompaniesByUser(string userName)
        {
            DataTable table2;
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_UserName", userName) };
            SqlConnection SSConnection = SSDataAccess.SSConnection;
            DataTable dataTable = new DataTable("VBSP");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "DmDvCs_GetByUser";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = SSConnection;
                command.Parameters.AddRange(values);
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
                table2 = dataTable;
            }
            catch (Exception exception)
            {
                log.ErrorException(string.Empty, exception); 
                throw new NotSupportedException("Invalid expression", exception);
            }
            finally
            {
                SSDataAccess.Close(SSConnection);
            }
            return table2;
        }

        public DataTable GetCompanies(string ps_Code)
        {
            DataTable table2;
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Ma_Dvcs", ps_Code) };
            SqlConnection SSConnection = SSDataAccess.SSConnection;
            DataTable dataTable = new DataTable("VBSP");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "DmDvCs_Get";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = SSConnection;
                command.Parameters.AddRange(values);
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
                table2 = dataTable;
            }
            catch (Exception exception)
            {
                log.ErrorException(string.Empty, exception); 
                throw new NotSupportedException("Invalid expression", exception);
            }
            finally
            {
                SSDataAccess.Close(SSConnection);
            }
            return table2;
        }

        public string GetSubsidiaries(string ps_UserName)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_UserName", ps_UserName), new SqlParameter("p_Ma_Dvcs_List", null) };
            values[1].Direction = ParameterDirection.Output;
            values[1].SqlDbType = SqlDbType.NVarChar;
            values[1].Size = 0xfe;
            SqlConnection SSConnection = SSDataAccess.SSConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Ma_Dvcs_Get";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = SSConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                log.ErrorException(string.Empty, exception); 
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            finally
            {
                SSDataAccess.Close(SSConnection);
            }
            return (string) values[1].Value;
        }

        public string GetSubsidiariesNew(string p_Ma_Dvcs)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Ma_Dvcs", p_Ma_Dvcs), new SqlParameter("p_Ma_Dvcs_List", null) };
            values[1].Direction = ParameterDirection.Output;
            values[1].SqlDbType = SqlDbType.NVarChar;
            values[1].Size = 0xfe;
            SqlConnection SSConnection = SSDataAccess.SSConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Lay_DvCs_Th";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = SSConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                log.ErrorException(string.Empty, exception); 
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            finally
            {
                SSDataAccess.Close(SSConnection);
            }
            return (string)values[1].Value;
        }

        public void SetCompany(DataRow row)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Stt", row["Stt"]), new SqlParameter("p_Ten_DvCs", row["Ten_Dvcs"]), new SqlParameter("p_Ten_DvCs_E", row["Ten_Dvcs_E"]), new SqlParameter("p_Dia_Chi", row["Dia_Chi"]), new SqlParameter("p_Dia_Chi_E", row["Dia_Chi_E"]), new SqlParameter("p_Ms_Thue", row["Ms_Thue"]), new SqlParameter("p_Tk_NH", row["Tk_NH"]), new SqlParameter("p_Chu_Tk", row["Chu_Tk"]), new SqlParameter("p_Ten_NH", row["Ten_NH"]), new SqlParameter("p_Ten_TP", row["Ten_TP"]), new SqlParameter("p_Ma_DvCs_Me", row["Ma_DvCs_Me"]), new SqlParameter("p_Ngay_Bd_Ht", row["Ngay_Bd_Ht"]) };
            SqlConnection SSConnection = SSDataAccess.SSConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "DmDvCs_Save";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = SSConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                log.ErrorException(string.Empty, exception); 
                throw new NotSupportedException("Invalid expression", exception);
            }
            finally
            {
                SSDataAccess.Close(SSConnection);
            }
        }

        public void SetEndDate(string ps_Subsidiary, DateTime pd_EndDate)
        {
            SqlParameter[] values = new SqlParameter[] { new SqlParameter("p_Ma_Dvcs", ps_Subsidiary), new SqlParameter("p_Ngay_Kt_Ht", pd_EndDate) };
            SqlConnection SSConnection = SSDataAccess.SSConnection;
            SqlCommand command = new SqlCommand();
            try
            {
                command.CommandText = "Khoa_So_Save";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = SSConnection;
                command.Parameters.AddRange(values);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                log.ErrorException(string.Empty, exception); 
                throw new NotSupportedException("Invalid expression: ", exception);
            }
            finally
            {
                SSDataAccess.Close(SSConnection);
            }
        }
    }
}

